USE [TaskManager]
GO
/****** Object:  StoredProcedure [dbo].[Appointments_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Appointments_Delete]
	@Id			int
AS
DELETE FROM [dbo].[Appointments]
      WHERE Id=@id

GO
/****** Object:  StoredProcedure [dbo].[Appointments_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Appointments_GetAll]
AS
BEGIN
	SELECT [Id]
		  ,[Title]
		  ,[Description]
		  ,[StartDate]
		  ,[EndDate]
		  ,[CreateDate]
		  ,[ModifyDate]
		  ,[CreateBy]
		  ,[ModifyBy]
	  FROM [dbo].[Appointments]
END


GO
/****** Object:  StoredProcedure [dbo].[Appointments_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Appointments_GetById]
	@Id		int
AS
BEGIN
	SELECT [Id]
		  ,[Title]
		  ,[Description]
		  ,[StartDate]
		  ,[EndDate]
		  ,[CreateDate]
		  ,[ModifyDate]
		  ,[CreateBy]
		  ,[ModifyBy]
	 FROM [dbo].[Appointments]
	 WHERE Id=@Id
END



GO
/****** Object:  StoredProcedure [dbo].[Appointments_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Appointments_Insert]
			(@Title			 nvarchar(200)
           ,@Description	 nvarchar(500)
           ,@StartDate		 datetime
           ,@EndDate		 datetime
           ,@CreateDate		 datetime
           ,@ModifyDate		 datetime
           ,@CreateBy		 int
           ,@ModifyBy		 int
           ,@Id INT OUTPUT)
AS
BEGIN
	INSERT INTO [dbo].[Appointments]
	           ([Title]
	           ,[Description]
	           ,[StartDate]
	           ,[EndDate]
	           ,[CreateDate]
	           ,[ModifyDate]
	           ,[CreateBy]
	           ,[ModifyBy])
	     VALUES
	           (@Title			
	           ,@Description	
	           ,@StartDate		
	           ,@EndDate		
	           ,@CreateDate		
	           ,@ModifyDate		
	           ,@CreateBy		
	           ,@ModifyBy	)	
	           
	           SELECT @Id = @@IDENTITY
END



GO
/****** Object:  StoredProcedure [dbo].[Appointments_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Appointments_Update]
		   (@Id				 int
		   ,@Title			 nvarchar(200)
           ,@Description	 nvarchar(500)
           ,@StartDate		 datetime
           ,@EndDate		 datetime
           ,@CreateDate		 datetime
           ,@ModifyDate		 datetime
           ,@CreateBy		 int
           ,@ModifyBy		 int)
AS
BEGIN
	UPDATE [dbo].[Appointments]
	   SET [Title]			= @Title		
	      ,[Description]	= @Description
	      ,[StartDate]		= @StartDate	
	      ,[EndDate]		= @EndDate	
	      ,[CreateDate]		= @CreateDate	
	      ,[ModifyDate]		= @ModifyDate	
	      ,[CreateBy]		= @CreateBy	
	      ,[ModifyBy]		= @ModifyBy	
	 WHERE Id=@Id
	 DELETE FROM Attendees WHERE AppointmentId = @Id
END



GO
/****** Object:  StoredProcedure [dbo].[AssignTasks_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AssignTasks_Delete]
	@Id					 int
AS
BEGIN
	DELETE FROM [dbo].[AssignTasks]
	WHERE Id=@Id
	
	-- DELETE report
	DELETE FROM Reports WHERE AssignTaskId = @Id
END

GO
/****** Object:  StoredProcedure [dbo].[AssignTasks_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AssignTasks_GetAll]
AS
BEGIN
	SELECT [Id]
		  ,[TaskId]
		  ,[UserId]
		  ,[Requirement]
		  ,[CompletedPercent]
		  ,[StartDate]
		  ,[EndDate]
		  ,[CreateDate]
		  ,[ModifyDate]
		  ,[CreateBy]
		  ,[ModifyBy]
	  FROM [dbo].[AssignTasks]
END



GO
/****** Object:  StoredProcedure [dbo].[AssignTasks_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AssignTasks_GetById]
	@Id			int
AS
BEGIN
	SELECT [Id]
		  ,[TaskId]
		  ,[UserId]
		  ,[Requirement]
		  ,[CompletedPercent]
		  ,[StartDate]
		  ,[EndDate]
		  ,[CreateDate]
		  ,[ModifyDate]
		  ,[CreateBy]
		  ,[ModifyBy]
	FROM [dbo].[AssignTasks]
	WHERE Id=@Id
END



GO
/****** Object:  StoredProcedure [dbo].[AssignTasks_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AssignTasks_Insert]
		  (@TaskId				 int
           ,@UserId				 int
           ,@Requirement		 nvarchar(500)
           ,@CompletedPercent	 int
		   ,@StartDate			 datetime
		   ,@EndDate			 datetime
           ,@CreateDate			 datetime
           ,@ModifyDate			 datetime
           ,@CreateBy			 int
           ,@ModifyBy			 int)
AS
BEGIN
	INSERT INTO [dbo].[AssignTasks]
	           ([TaskId]
	           ,[UserId]
	           ,[Requirement]
	           ,[CompletedPercent]
	           ,[StartDate]
			   ,[EndDate]
			   ,[CreateDate]
	           ,[ModifyDate]
	           ,[CreateBy]
	           ,[ModifyBy])
	     VALUES
	           (@TaskId				 
	           ,@UserId				 
	           ,@Requirement		 
	           ,@CompletedPercent
			   ,@StartDate
			   ,@EndDate	
	           ,@CreateDate			 
	           ,@ModifyDate			 
	           ,@CreateBy			 
	           ,@ModifyBy			 )
END



GO
/****** Object:  StoredProcedure [dbo].[AssignTasks_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AssignTasks_Update]
			
		  ( @Id					 int
		   ,@TaskId				 int
           ,@UserId				 int
           ,@Requirement		 nvarchar(500)
           ,@CompletedPercent	 int
		   ,@StartDate			 datetime
		   ,@EndDate			 datetime
           ,@CreateDate			 datetime
           ,@ModifyDate			 datetime
           ,@CreateBy			 int
           ,@ModifyBy			 int)
AS
BEGIN
	UPDATE [dbo].[AssignTasks]
	   SET [TaskId]				= @TaskId				
		  ,[UserId]				= @UserId				
		  ,[Requirement]		= @Requirement		
		  ,[CompletedPercent]	= @CompletedPercent	
		  ,[StartDate]			= @StartDate
		  ,[EndDate]			= @EndDate
		  ,[CreateDate]			= @CreateDate			
		  ,[ModifyDate]			= @ModifyDate			
		  ,[CreateBy]			= @CreateBy			
		  ,[ModifyBy]			= @ModifyBy			
	 WHERE Id=@Id
END


GO
/****** Object:  StoredProcedure [dbo].[ChatMessage_UpdateIsRead]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessage_UpdateIsRead]
@ChatRoomId INT,
@ToUserId INT
AS
BEGIN
        UPDATE ChatMessages SET IsRead = 1 WHERE @ChatRoomId = RoomId AND ToUserId = @ToUserId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_CountUnreadMessage]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_CountUnreadMessage]
@ChatRoomId INT,
@ToUserId INT
AS
BEGIN
        SELECT COUNT(*) FROM ChatMessages WHERE RoomId = @ChatRoomId AND ToUserId = @ToUserId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_GetById]
@Id INT
AS
BEGIN
        SELECT  * FROM ChatMessages WHERE Id = @Id
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_GetByRoomId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_GetByRoomId]
@ChatRoomId INT
AS
BEGIN
        SELECT * FROM ChatMessages WHERE RoomId = @ChatRoomId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_GetHistoriesByTop]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_GetHistoriesByTop]
@ChatRoomId INT,
@LastMessageId BIGINT,
@Top INT
AS
BEGIN
        SELECT TOP(@Top) * FROM ChatMessages WHERE RoomId = @ChatRoomId AND @LastMessageId > Id ORDER BY Id DESC 
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_GetLastestByRoomId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_GetLastestByRoomId]
@ChatRoomId INT
AS
BEGIN
        SELECT TOP 1 * FROM ChatMessages WHERE RoomId = @ChatRoomId ORDER BY Id DESC
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_Insert]
@RoomId INT,
@FromUserId INT,
@ToUserId INT,
@Message NVARCHAR(1000),
@CreateDate DATETIME,
@CreateDateStamp BIGINT,
@IsRead BIT,
@Status INT,
@Id BIGINT OUTPUT
AS
BEGIN
        INSERT INTO [TaskManager].[dbo].[ChatMessages]
           ([RoomId]
           ,[Message]
           ,[FromUserId]
           ,[ToUserId]
           ,[CreateDate]
           ,[CreateDateStamp]
           ,[IsRead]
           ,[Status])
     VALUES
           (@RoomId
           ,@Message
           ,@FromUserId
           ,@ToUserId
           ,@CreateDate
           ,@CreateDateStamp
           ,@IsRead
           ,@Status)
     SELECT @Id = @@IDENTITY
END

GO
/****** Object:  StoredProcedure [dbo].[ChatRooms_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatRooms_GetById]
@RoomId INT
AS
BEGIN
        SELECT * FROM ChatRooms WHERE Id = @RoomId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatRooms_GetByUserId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatRooms_GetByUserId]
@UserId INT
AS
BEGIN
        SELECT * FROM ChatRooms WHERE FromUserId = @UserId OR ToUserId = @UserId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatRooms_GetByUsers]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*Chat proc*/

CREATE PROC [dbo].[ChatRooms_GetByUsers]
@FromUserId INT,
@ToUserId INT
AS
BEGIN
        SELECT * FROM ChatRooms WHERE FromUserId = @FromUserId AND ToUserId = @ToUserId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatRooms_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatRooms_Insert]
@FromUserId INT,
@ToUserId INT,
@CreateDate DATETIME,
@Id int OUTPUT
AS
BEGIN
        INSERT INTO [ChatRooms]
           ([FromUserId]
           ,[ToUserId]
           ,[CreateDate])
     VALUES
           (@FromUserId
           ,@ToUserId
           ,@CreateDate)
    SELECT @Id = @@IDENTITY
END

GO
/****** Object:  StoredProcedure [dbo].[ChatRooms_SearchAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatRooms_SearchAll]
@FromDate DATETIME,
@ToDate DATETIME,
@UserId INT
AS
BEGIN
        SELECT * FROM ChatRooms WHERE (FromUserId = @UserId OR ToUserId = @UserId) AND CreateDate BETWEEN @FromDate AND @ToDate 
END

GO
/****** Object:  StoredProcedure [dbo].[Departments_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Departments_Delete]

        @Id             int

AS

BEGIN

        DELETE FROM [dbo].[Departments]

        WHERE Id=@Id

END










GO
/****** Object:  StoredProcedure [dbo].[Departments_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Departments_GetAll]

AS

BEGIN

        SELECT [Id]

                  ,[Name]

                  ,[UserId]

                  ,[CreateDate]

                  ,[ModifyDate]

                  ,[CreateBy]

                  ,[ModifyBy]

          FROM [dbo].[Departments]

END










GO
/****** Object:  StoredProcedure [dbo].[Departments_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Departments_GetById]

        @DepartmentId int

AS

BEGIN

        SELECT [Id]

                  ,[Name]

                  ,[UserId]

                  ,[CreateDate]

                  ,[ModifyDate]

                  ,[CreateBy]

                  ,[ModifyBy]

        FROM [dbo].[Departments]

        WHERE Id=@DepartmentId

END










GO
/****** Object:  StoredProcedure [dbo].[Departments_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Departments_Insert]

                    @Name                nvarchar(100)  

           ,@UserId              int  

           ,@CreateDate  datetime  

           ,@ModifyDate  datetime  

           ,@CreateBy    int  

           ,@ModifyBy    int  

AS

BEGIN

        INSERT INTO [dbo].[Departments]

                           ([Name]

                           ,[UserId]

                           ,[CreateDate]

                           ,[ModifyDate]

                           ,[CreateBy]

                           ,[ModifyBy])

                 VALUES

                           (@Name                

                           ,@UserId              

                           ,@CreateDate  

                           ,@ModifyDate  

                           ,@CreateBy    

                           ,@ModifyBy)

END 




GO
/****** Object:  StoredProcedure [dbo].[Departments_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Departments_Update]

                        @Id                      int

                   ,@Name                nvarchar(100)  

           ,@UserId              int  

           ,@CreateDate  datetime  

           ,@ModifyDate  datetime  

           ,@CreateBy    int  

           ,@ModifyBy    int 

AS

BEGIN

        UPDATE [dbo].[Departments]

           SET [Name]           = @Name

                  ,[UserId]             = @UserId

                  ,[CreateDate] = @CreateDate

                  ,[ModifyDate] = @ModifyDate

                  ,[CreateBy]   = @CreateBy

                  ,[ModifyBy]   = @ModifyBy

        WHERE Id=@Id

END










GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_DeleteByReportId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_DeleteByReportId]

        @ReportId               int

AS

BEGIN

        DELETE FROM [dbo].[ReportDocs]

        WHERE ReportId=@ReportId

END

GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_GetAll]

AS

BEGIN

        SELECT [ReportId]

                  ,[DocumentId]

        FROM [dbo].[ReportDocs]

END





-------------




GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_GetByDocumentId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_GetByDocumentId]

        @DocumentId int

AS

BEGIN

  SELECT [ReportId]

      ,[DocumentId]

  FROM [dbo].[ReportDocs]

  WHERE DocumentId=@DocumentId

END



-------------




GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_GetByReportId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_GetByReportId]

        @ReportId int

AS

BEGIN

  SELECT [ReportId]

      ,[DocumentId]

  FROM [dbo].[ReportDocs]

  WHERE ReportId=@ReportId

END

------------




GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_Insert]

                   (@ReportId            int

           ,@DocumentId          int)

AS

INSERT INTO [dbo].[ReportDocs]

           ([ReportId]

           ,[DocumentId])

     VALUES

           (@ReportId   

           ,@DocumentId)


GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_UpdateByDocumentId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_UpdateByDocumentId]

        @ReportId               int,

        @DocumentId             int

AS

BEGIN

        UPDATE [dbo].[ReportDocs]

           SET [ReportId] = @ReportId

        WHERE DocumentId=@DocumentId

END

-----------




GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_UpdateByReportId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_UpdateByReportId]

        @ReportId               int,

        @DocumentId             int

AS

BEGIN

        UPDATE [dbo].[ReportDocs]

           SET [DocumentId] = @DocumentId

        WHERE ReportId=@ReportId

END

----------




GO
/****** Object:  StoredProcedure [dbo].[Reports_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Reports_Delete]

        @Id             int

AS

BEGIN

        DELETE FROM [dbo].[Reports]

        WHERE Id=@Id

END

----------




GO
/****** Object:  StoredProcedure [dbo].[Reports_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Reports_GetAll]
AS
BEGIN
        SELECT [Id]
				  ,[AssignTaskId]
                  ,[TaskId]
                  ,[ReportDate]
                  ,[ReportResult]
                  ,[NextTask]
				  ,[CompletedPercent]
                  ,[UserReport]
				  ,[Comment]
				  ,[CommentBy]
				  ,[CommentDate]
				  ,[ReportType]
                  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
        FROM [dbo].[Reports]
END
-------------




GO
/****** Object:  StoredProcedure [dbo].[Reports_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Reports_GetById]
        @Id             int
AS
BEGIN
        SELECT [Id]
        
				  ,[AssignTaskId]
				  ,[TaskId]
                  ,[ReportDate]
                  ,[ReportResult]
                  ,[NextTask]
                  ,[CompletedPercent]
                  ,[UserReport]
				  ,[Comment]
				  ,[CommentBy]
				  ,[CommentDate]
				  ,[ReportType]
                  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
          FROM [dbo].[Reports]
          WHERE Id=@Id
END

GO
/****** Object:  StoredProcedure [dbo].[Reports_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Reports_Insert]
                   (
            @AssignTaskId                             int
           ,@TaskId                             int
           ,@ReportDate                 datetime
           ,@ReportResult               nvarchar(500)
           ,@NextTask                   nvarchar(500)
           ,@CompletedPercent			int
           ,@UserReport                 int
		   ,@Comment					nvarchar(500)
		   ,@CommentBy					int
		   ,@CommentDate				datetime
		   ,@ReportType					int
           ,@CreateDate                 datetime
           ,@ModifyDate                 datetime
           ,@CreateBy                   int
           ,@ModifyBy                   int
           ,@Id INT OUTPUT)
AS
BEGIN
        INSERT INTO [dbo].[Reports]
                           (
                           [AssignTaskId]
                           ,[TaskId]
                           ,[ReportDate]
                           ,[ReportResult]
                           ,[NextTask]
                           ,[CompletedPercent]
                           ,[UserReport]
						   ,[Comment]
						   ,[CommentBy]
						   ,[CommentDate]
						   ,[ReportType]
                           ,[CreateDate]
                           ,[ModifyDate]
                           ,[CreateBy]
                           ,[ModifyBy])
                 VALUES
                           (
                           @AssignTaskId
                           ,@TaskId                             
                           ,@ReportDate                 
                           ,@ReportResult               
                           ,@NextTask                   
                           ,@CompletedPercent   
                           ,@UserReport 
						   ,@Comment
						   ,@CommentBy
						   ,@CommentDate
						   ,@ReportType
                           ,@CreateDate                 
                           ,@ModifyDate                 
                           ,@CreateBy                   
                           ,@ModifyBy           )       
                           
                           SELECT @Id = @@IDENTITY

END





--------------




GO
/****** Object:  StoredProcedure [dbo].[Reports_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Reports_Update]

		   (@Id                         int
		   ,@AssignTaskId                             int
           ,@TaskId                     int
           ,@ReportDate                 datetime
           ,@ReportResult               nvarchar(500)
           ,@NextTask                   nvarchar(500)
           ,@CompletedPercent			int
           ,@UserReport                 int
		   ,@Comment					nvarchar(500)
		   ,@CommentBy					int
		   ,@CommentDate				datetime
		   ,@ReportType					int
           ,@CreateDate                 datetime
           ,@ModifyDate                 datetime
           ,@CreateBy                   int
           ,@ModifyBy                   int)
AS
BEGIN
        UPDATE [dbo].[Reports]                                          
           SET	   [TaskId]                     = @TaskId 
				  ,[AssignTaskId]					= @AssignTaskId
                  ,[ReportDate]                 = @ReportDate                   
                  ,[ReportResult]               = @ReportResult         
                  ,[NextTask]                   = @NextTask                     
                  ,[CompletedPercent]			= @CompletedPercent     
                  ,[UserReport]                 = @UserReport                   
				  ,[Comment]					= @Comment
				  ,[CommentBy]					= @CommentBy
				  ,[CommentDate]				= @CommentDate
				  ,[ReportType]					= @ReportType
                  ,[CreateDate]                 = @CreateDate                   
                  ,[ModifyDate]                 = @ModifyDate                   
                  ,[CreateBy]                   = @CreateBy                     
                  ,[ModifyBy]                   = @ModifyBy                     
         WHERE Id=@Id
END



GO
/****** Object:  StoredProcedure [dbo].[TaskDocs_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[TaskDocs_Delete]

        @TaskId int

AS

BEGIN

        DELETE FROM [dbo].[TaskDocs]

        WHERE TaskId=@TaskId

END

--------------




GO
/****** Object:  StoredProcedure [dbo].[TaskDocs_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[TaskDocs_GetAll]

AS

BEGIN

        SELECT [TaskId]

                  ,[DocumentId]

          FROM [dbo].[TaskDocs]

END





------------




GO
/****** Object:  StoredProcedure [dbo].[TaskDocs_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[TaskDocs_Insert]

        @TaskId         int,

        @DocumentId int

AS

BEGIN

        INSERT INTO [dbo].[TaskDocs]

                           ([TaskId]

                           ,[DocumentId])

        VALUES

                           (@TaskId

                           ,@DocumentId)

END



--------




GO
/****** Object:  StoredProcedure [dbo].[TaskDocs_UpdateByTaskId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[TaskDocs_UpdateByTaskId]

        @TaskId         int,

        @DocumentId int

AS

BEGIN

        UPDATE [dbo].[TaskDocs]

           SET [TaskId]         = @TaskId

                  ,[DocumentId] = @DocumentId

        WHERE TaskId=@TaskId

END





----------




GO
/****** Object:  StoredProcedure [dbo].[Tasks_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Tasks_Delete]

        @Id             int

AS

BEGIN

		DECLARE @AssignTaskId INT

		DECLARE task_cursor CURSOR FOR 
		SELECT Id
		FROM AssignTasks
		WHERE TaskId = @Id

		OPEN task_cursor
		FETCH NEXT FROM task_cursor 
		INTO @AssignTaskId

		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			-- Delete report
			DELETE FROM Reports WHERE AssignTaskId = @AssignTaskId
			
			-- Delete assign task
			DELETE FROM AssignTasks WHERE Id = @AssignTaskId
			
			FETCH NEXT FROM task_cursor 
			INTO @AssignTaskId
		END 
		CLOSE task_cursor
		DEALLOCATE task_cursor

		-- Delete Task
        DELETE FROM [dbo].[Tasks]
        WHERE Id=@Id

END

----------




GO
/****** Object:  StoredProcedure [dbo].[Tasks_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Tasks_GetAll]
AS
SELECT [Id]
      ,[ProjectId]
      ,[Name]
      ,[Leader]
      ,[Priority]
      ,[Description]
      ,[StartDate]
      ,[EndDate]
      ,[CreateDate]
      ,[ModifyDate]
      ,[CreateBy]
      ,[ModifyBy]
  FROM [dbo].[Tasks]

GO
/****** Object:  StoredProcedure [dbo].[Tasks_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Tasks_GetById]
        @Id             int
AS
BEGIN
        SELECT		[Id]
				  ,[ProjectId]
                  ,[Name]
				  ,[Leader]
				  ,[Priority]
                  ,[Description]
				  ,[StartDate]
				  ,[EndDate]
				  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
        FROM [dbo].[Tasks]
        WHERE Id=@Id
END




GO
/****** Object:  StoredProcedure [dbo].[Tasks_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Tasks_Insert]
						   (@ProjectId	 int
                           ,@Name        nvarchar(200)
						   ,@Leader		 int
						   ,@Priority	 int
                           ,@Description nvarchar(500)
						   ,@StartDate	 Datetime
						   ,@EndDate	 Datetime
                           ,@CreateDate  datetime
                           ,@ModifyDate  datetime
                           ,@CreateBy    int
                           ,@ModifyBy    int
                           ,@Id INT OUTPUT
                           )

AS

BEGIN

        INSERT INTO [dbo].[Tasks]

                           ([ProjectId]
						   ,[Name]
						   ,[Leader]
						   ,[Priority]
                           ,[Description]
						   ,[StartDate]
						   ,[EndDate]
                           ,[CreateDate]
                           ,[ModifyDate]
                           ,[CreateBy]
                           ,[ModifyBy])

                 VALUES

                           (@ProjectId
						   ,@Name                
						   ,@Leader
						   ,@Priority
                           ,@Description 
						   ,@StartDate
						   ,@EndDate
                           ,@CreateDate  
                           ,@ModifyDate  
                           ,@CreateBy    
                           ,@ModifyBy    )
         SELECT @Id = @@IDENTITY
END




GO
/****** Object:  StoredProcedure [dbo].[Tasks_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Tasks_Update]

                           (@Id                  int
						   ,@ProjectId			 int
                           ,@Name                nvarchar(200)
						   ,@Leader		 int
						   ,@Priority	 int
                           ,@Description nvarchar(500)
						   ,@StartDate	 Datetime
						   ,@EndDate	 Datetime
                           ,@CreateDate  datetime
                           ,@ModifyDate  datetime
                           ,@CreateBy    int
                           ,@ModifyBy    int)

AS
BEGIN
        UPDATE [dbo].[Tasks]
           SET	  [ProjectId]	= @ProjectId
				  ,[Name]       = @Name                        
				  ,[Leader]		 = @Leader
				  ,[Priority]	 = @Priority
                  ,[Description] = @Description         
				  ,[StartDate]   = @StartDate
				  ,[EndDate]	 = @EndDate
                  ,[CreateDate]  = @CreateDate
                  ,[ModifyDate]  = @ModifyDate  
                  ,[CreateBy]    = @CreateBy    
                  ,[ModifyBy]    = @ModifyBy    
         WHERE Id=@Id
END

-----------------




GO
/****** Object:  StoredProcedure [dbo].[Users_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_Delete]

        @Id     int

AS

BEGIN

        DELETE FROM [dbo].[Users]

        WHERE Id=@Id

END










GO
/****** Object:  StoredProcedure [dbo].[Users_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_GetAll]

AS

BEGIN

        SELECT [Id]

                  ,[UserName]

                  ,[Password]

                  ,[FirstName]

                  ,[LastName]

                  ,[Address]

                  ,[DateOfBirth]

                  ,[Gender]

                  ,[DepartmentId]
				  ,[Email]
				  ,[Mission]
                  ,[Avatar]

                  ,[IsActive]

                  ,[IsAdmin]
				  ,[IsManager]
                  ,[CreateDate]

                  ,[ModifyDate]

                  ,[CreateBy]

                  ,[ModifyBy]

          FROM [dbo].[Users]

END










GO
/****** Object:  StoredProcedure [dbo].[Users_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_GetById]

        @Id             int

AS

BEGIN

        SELECT [Id]

                  ,[UserName]

                  ,[Password]

                  ,[FirstName]

                  ,[LastName]

                  ,[Address]

                  ,[DateOfBirth]

                  ,[Gender]

                  ,[DepartmentId]
				  ,[Email]
				  ,[Mission]
                  ,[Avatar]

                  ,[IsActive]

                  ,[IsAdmin]
				  ,[IsManager]
                  ,[CreateDate]

                  ,[ModifyDate]

                  ,[CreateBy]

                  ,[ModifyBy]

        FROM [dbo].[Users]

        WHERE Id=@Id

END










GO
/****** Object:  StoredProcedure [dbo].[Users_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_Insert]

            (@UserName            varchar(100)  

           ,@Password            varchar(100)  

           ,@FirstName           nvarchar(100)  

           ,@LastName            nvarchar(100)  

           ,@Address             nvarchar(500)  

           ,@DateOfBirth         date  

           ,@Gender                      bit  

           ,@DepartmentId        int  
		   ,@Email				 nvarchar(50)
		   ,@Mission			 nvarchar(100)
           ,@Avatar              nvarchar(200)  

           ,@IsActive            bit  

           ,@IsAdmin             bit  
		   ,@IsManager		     bit
           ,@CreateDate          datetime  

           ,@ModifyDate          datetime  

           ,@CreateBy            int  

           ,@ModifyBy            int  )

AS

BEGIN

        INSERT INTO [dbo].[Users]

                           ([UserName]

                           ,[Password]

                           ,[FirstName]

                           ,[LastName]

                           ,[Address]

                           ,[DateOfBirth]

                           ,[Gender]

                           ,[DepartmentId]
						   ,[Email]
						   ,[Mission]
						   ,[Avatar]

                           ,[IsActive]

                           ,[IsAdmin]
						   ,[IsManager]
                           ,[CreateDate]

                           ,[ModifyDate]

                           ,[CreateBy]

                           ,[ModifyBy])

                 VALUES

                           (@UserName           

                           ,@Password           

                           ,@FirstName          

                           ,@LastName           

                           ,@Address            

                           ,@DateOfBirth        

                           ,@Gender                     

                           ,@DepartmentId       
						   ,@Email
						   ,@Mission
                           ,@Avatar                     

                           ,@IsActive           

                           ,@IsAdmin            
						   ,@IsManager
                           ,@CreateDate         

                           ,@ModifyDate         

                           ,@CreateBy           

                           ,@ModifyBy           )

END




GO
/****** Object:  StoredProcedure [dbo].[Users_Login]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Users_Login]
@UserName VARCHAR(100),
@Password VARCHAR(100)
AS
BEGIN
        SELECT * FROM Users WHERE LOWER(UserName) = @UserName AND Password = @Password AND IsActive = 1
END

GO
/****** Object:  StoredProcedure [dbo].[Users_Search]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_Search]
@UserName VARCHAR(100),
@FullName NVARCHAR(200),
@DepartmentId INT
AS
BEGIN
        DECLARE @sql NVARCHAR(MAX) = ''
        DECLARE @where NVARCHAR(MAX) = ' WHERE 1 = 1 '
        SET @sql += 'SELECT * FROM Users '
        IF      @DepartmentId != 0
        BEGIN
                SET @where += ' AND DepartmentId = ' + convert(varchar, @DepartmentId)
        END
        
        IF @UserName IS NOT NULL
        BEGIN
                SET @where += ' AND LOWER(UserName) Like ''%' + @UserName + '%'''
        END
        
        IF @FullName IS NOT NULL
        BEGIN
                SET @where += ' AND LOWER(FirstName + LastName) Like ''%' + @FullName + '%'''
        END
        
        EXEC (@sql + @where)
        
END

GO
/****** Object:  StoredProcedure [dbo].[Users_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_Update]

                   (@Id                          int

                   ,@UserName            varchar(100)  

           ,@Password            varchar(100)  

           ,@FirstName           nvarchar(100)  

           ,@LastName            nvarchar(100)  

           ,@Address             nvarchar(500)  

           ,@DateOfBirth         date  

           ,@Gender                      bit  

           ,@DepartmentId        int  
		   ,@Email				 nvarchar(50)
		   ,@Mission			 nvarchar(100)
           ,@Avatar                      nvarchar(200)  

           ,@IsActive            bit  

           ,@IsAdmin             bit  
		   ,@IsManager			 bit
           ,@CreateDate          datetime  

           ,@ModifyDate          datetime  

           ,@CreateBy            int  

           ,@ModifyBy            int  )

AS

BEGIN

        UPDATE [dbo].[Users]

           SET [UserName]               = @UserName     

                  ,[Password]           = @Password     

                  ,[FirstName]          = @FirstName    

                  ,[LastName]           = @LastName     

                  ,[Address]            = @Address      

                  ,[DateOfBirth]        = @DateOfBirth

                  ,[Gender]                     = @Gender               

                  ,[DepartmentId]       = @DepartmentId
				  ,[Email]				= @Email 
				  ,[Mission]			= @Mission
                  ,[Avatar]                     = @Avatar               

                  ,[IsActive]           = @IsActive     

                  ,[IsAdmin]            = @IsAdmin      
				  ,[IsManager]			= @IsManager
                  ,[CreateDate]         = @CreateDate   

                  ,[ModifyDate]         = @ModifyDate   

                  ,[CreateBy]           = @CreateBy     

                  ,[ModifyBy]           = @ModifyBy     

         WHERE Id=@Id

END











GO
/****** Object:  StoredProcedure [dbo].[Users_UpdatePassword]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_UpdatePassword]

        @Id                             int,

        @Password               varchar(100)

AS

BEGIN

        UPDATE [dbo].[Users]

           SET [Password] = @Password

        WHERE Id=@Id

END






GO
CREATE PROC Departments_GetByUserId
	@UserId		int
AS
BEGIN
	SELECT [Id]
		  ,[Name]
		  ,[UserId]
		  ,[CreateDate]
		  ,[ModifyDate]
		  ,[CreateBy]
		  ,[ModifyBy]
	FROM [dbo].[Departments]
	WHERE UserId=@UserId
END
GO

CREATE PROC [dbo].[Tasks_Search]
	@Name			VARCHAR(200),
	@Leader			INT,
	@StartDate		DATETIME,
	@EndDate		DATETIME
AS
BEGIN
	DECLARE	@Sql	NVARCHAR(4000)
    DECLARE	@Where	NVARCHAR(1000)
    IF  ((@StartDate IS NOT NULL) AND (@EndDate IS NOT NULL) AND (@StartDate <= @EndDate))
	BEGIN
		SET @Where	=			 ' AND StartDate >= '''	+ CONVERT(VARCHAR, @StartDate, 121) + ''''
		SET @Where	=	@Where + ' AND EndDate <= '''	+ CONVERT(VARCHAR, @EndDate, 121) + ''''
	END
		
    
	IF	(@Name IS NOT NULL AND @Name != '')
		SET	@Where	=	@Where + ' AND Name LIKE ''%' + @Name + '%'''
	IF	(@Leader > 0)
		SET	@Where	=	@Where + ' AND Leader = ' +	CONVERT(VARCHAR, @Leader)
	
	SET		@Sql	= 'SELECT  [Id]
							  ,[ProjectId]
							  ,[Name]
							  ,[Leader]
							  ,[Priority]
							  ,[Description]
							  ,[StartDate]
							  ,[EndDate]
							  ,[CreateDate]
							  ,[ModifyDate]
							  ,[CreateBy]
							  ,[ModifyBy]
						  FROM [dbo].[Tasks]
						WHERE		0	=	0
						' +	@Where
	EXECUTE	(@Sql)
END
GO
-----add Date 25/01/2015 by manhlt
CREATE PROC [dbo].[Departments_CheckExitUserId]
	@UserId		int
AS
BEGIN
	SELECT *
	FROM [dbo].[Departments]
	WHERE UserId=@UserId
END
GO

CREATE PROC [AssignTasks_GetByTaskId]
@TaskId INT
AS
BEGIN
	SELECT * FROM AssignTasks WHERE TaskId = @TaskId
END
GO

CREATE PROC [AssignTasks_UpdateCompleted]
@Id INT,
@CompletedPercent INT
AS
BEGIN 
	UPDATE AssignTasks SET CompletedPercent = @CompletedPercent WHERE @Id = Id
END
GO

CREATE PROC Users_GetByDepartmentId
@DepartmentId INT
AS 
BEGIN
	SELECT * FROM Users WHERE DepartmentId = @DepartmentId AND IsActive = 1
END
GO

CREATE PROC Documents_Insert
@Name NVARCHAR(200),
@FilePath NVARCHAR(500),
@Id INT OUTPUT
AS
BEGIN
INSERT INTO [dbo].[Documents]
           ([Name]
           ,[FilePath])
     VALUES
           (@Name
           ,@FilePath)
     SELECT @Id = @@IDENTITY
END
GO

CREATE PROC Documents_GetByIds
@Ids VARCHAR(2000)
AS
BEGIN
	EXEC('SELECT * FROM Documents WHERE Id IN (' +  @Ids + ')')
END
GO

create PROC Appointments_GetByUserId
@UserId INT
AS
BEGIN
	SELECT A.* 
	FROM Appointments as A
	WHERE A.CreateBy = @UserId 
		  OR 0 < (SELECT COUNT(*) FROM Attendees WHERE AppointmentId = A.Id AND UserId = @UserId)
		  and not exists (select 1 from AppointmentComments ac where ac.AppointmentId = A.Id and CommentType = 1)
END
GO

CREATE PROC Attendees_Insert
	@AppointmentId INT,
	@UserId INT
AS
BEGIN
INSERT INTO .[dbo].[Attendees]
           ([AppointmentId]
           ,[UserId])
     VALUES
           (@AppointmentId
           ,@UserId)
END
GO

CREATE PROC Attendees_GetByAppointmentId
	@AppointmentId INT
AS
BEGIN
	SELECT u.* FROM Attendees as a LEFT JOIN Users as u ON a.UserId = u.Id WHERE AppointmentId  = @AppointmentId
END
GO

CREATE PROC Reports_OverDues
	@DepartmentId INT,
	@Date VARCHAR(200)
AS
BEGIN
	SELECT * FROM Users WHERE DepartmentId = @DepartmentId AND Id NOT IN (SELECT UserReport FROM Reports WHERE CONVERT(VARCHAR(24),CreateDate,103) = @Date )
END

Go

CREATE PROC Attendees_Delete
	@AppointmentId INT
AS
BEGIN
	DELETE FROM Attendees WHERE AppointmentId = @AppointmentId
END
GO

CREATE PROC Reports_GetByUserReportAndReportDate
	@UserReport int,
	@Date VARCHAR(200)
AS
SELECT [Id]
      ,[AssignTaskId]
      ,[TaskId]
      ,[ReportDate]
      ,[ReportResult]
      ,[NextTask]
      ,[CompletedPercent]
      ,[UserReport]
      ,[Comment]
      ,[CommentBy]
      ,[CommentDate]
	  ,[ReportType]
      ,[CreateDate]
      ,[ModifyDate]
      ,[CreateBy]
      ,[ModifyBy]
 FROM [dbo].[Reports]
 WHERE UserReport=@UserReport
   AND CONVERT(VARCHAR(24),ReportDate,103) = @Date
GO

CREATE PROC [dbo].[Reports_Search]
	@UserId			INT,
	@StartDate		DATETIME,
	@EndDate		DATETIME
AS
BEGIN
	DECLARE	@Sql	NVARCHAR(4000)
    DECLARE	@Where	NVARCHAR(1000)
    IF  ((@StartDate IS NOT NULL) AND (@EndDate IS NOT NULL) AND (@StartDate <= @EndDate))
	BEGIN
		SET @Where	=			 ' AND ReportDate >= '''	+ CONVERT(VARCHAR, @StartDate, 121) + ''''
		SET @Where	=	@Where + ' AND ReportDate <= '''	+ CONVERT(VARCHAR, @EndDate, 121) + ''''
	END

	IF	(@UserId > 0)
		SET	@Where	=	@Where + ' AND UserReport = ' +	CONVERT(VARCHAR, @UserId)
	
	SET		@Sql	= 'SELECT  *
						  FROM [dbo].[Reports]
						WHERE		0	=	0
						' +	@Where
	EXECUTE	(@Sql)
END
GO
CREATE PROC [dbo].[AssignTasks_Search]
        @Requirement            VARCHAR(200),
        @UserId			        INT,
        @StartDate              DATETIME,
        @EndDate                DATETIME
AS
BEGIN
	DECLARE @Sql    NVARCHAR(4000)
    DECLARE     @Where  NVARCHAR(1000)
    IF  ((@StartDate IS NOT NULL) AND (@EndDate IS NOT NULL) AND (@StartDate <= @EndDate))
        BEGIN
                SET @Where      =       ' AND CreateDate >= ''' + CONVERT(VARCHAR, @StartDate, 121) + ''''
                SET @Where      =       @Where + ' AND CreateDate <= '''   + CONVERT(VARCHAR, @EndDate, 121) + ''''
        END
                
    
        IF      (@Requirement IS NOT NULL AND @Requirement != '')
                SET     @Where  =       @Where + ' AND Requirement LIKE ''%' + @Requirement + '%'''
        IF      (@UserId	 > 0)
                SET     @Where  =       @Where + ' AND UserId = ' +    CONVERT(VARCHAR, @UserId)	
        SET             @Sql    = 'SELECT  [Id]
										  ,[TaskId]
										  ,[UserId]
										  ,[Requirement]
										  ,[CompletedPercent]
										  ,[StartDate]
										  ,[EndDate]
										  ,[CreateDate]
										  ,[ModifyDate]
										  ,[CreateBy]
										  ,[ModifyBy]
									  FROM [dbo].[AssignTasks]		
                                      WHERE           0       =       0
                                                ' +     @Where
        EXECUTE (@Sql)
END

GO


CREATE PROC Alerts_GetByUserId
	@UserId INT
AS
BEGIN
	SELECT * FROM Alerts WHERE UserId = @UserId AND IsRead = 0
END
GO

CREATE PROC Alerts_Insert
	@Message				NVARCHAR(1000),
	@UserId				INT,
	@ReferenceId			INT,
	@TypeId				INT,
	@IsRead				BIT
AS
BEGIN
	INSERT INTO Alerts VALUES (@Message, @UserId, @ReferenceId, @TypeId, @IsRead)
END
GO

CREATE PROC Alerts_Delete
	@Id INT
	AS
	BEGIN 
		DELETE FROM Alerts WHERE Id = @Id
	END
GO

CREATE PROC Alerts_DeleteByType
	@TypeId INT,
	@ReferenceId INT
	AS
	BEGIN 
		DELETE FROM Alerts WHERE ReferenceId = @ReferenceId AND TypeId = @TypeId
	END
GO

CREATE PROC Projects_GetAll
	AS
	BEGIN
		SELECT [Id]
			  ,[Name]
			  ,[Description]
			  ,[StartDate]
			  ,[EndDate]
			  ,[CreateDate]
			  ,[ModifyDate]
			  ,[CreateBy]
			  ,[ModifyBy]
		FROM [dbo].[Projects]
	END
GO


CREATE PROC Projects_Insert
				   (@Name			nvarchar(200)
				   ,@Description	nvarchar(500)
				   ,@StartDate		datetime
				   ,@EndDate		datetime
				   ,@CreateDate		datetime
				   ,@ModifyDate		datetime
				   ,@CreateBy		int
				   ,@ModifyBy		int)
	AS
	BEGIN
		INSERT INTO [dbo].[Projects]
				   ([Name]
				   ,[Description]
				   ,[StartDate]
				   ,[EndDate]
				   ,[CreateDate]
				   ,[ModifyDate]
				   ,[CreateBy]
				   ,[ModifyBy])
			 VALUES
				   (@Name
				   ,@Description
				   ,@StartDate
				   ,@EndDate
				   ,@CreateDate
				   ,@ModifyDate
				   ,@CreateBy
				   ,@ModifyBy)
	END
GO


CREATE PROC Projects_Update
					(@Id				int
				   ,@Name			nvarchar(200)
				   ,@Description	nvarchar(500)
				   ,@StartDate		datetime
				   ,@EndDate		datetime
				   ,@CreateDate		datetime
				   ,@ModifyDate		datetime
				   ,@CreateBy		int
				   ,@ModifyBy		int)	
	AS
	BEGIN
		UPDATE [dbo].[Projects]
		   SET [Name]			= @Name
			  ,[Description]	= @Description
			  ,[StartDate]		= @StartDate
			  ,[EndDate]		= @EndDate
			  ,[CreateDate]		= @CreateDate
			  ,[ModifyDate]		= @ModifyDate
			  ,[CreateBy]		= @CreateBy
			  ,[ModifyBy]		= @ModifyBy
		WHERE Id=@Id
	END
GO

CREATE PROC Projects_Delete
	@Id		int
	AS
	BEGIN
		DELETE FROM [dbo].[Projects]
		WHERE Id=@Id
	END
GO


CREATE PROC [dbo].[Projects_Search]
	@Name			VARCHAR(200),
	@StartDate		DATETIME,
	@EndDate		DATETIME
AS
BEGIN
	DECLARE	@Sql	NVARCHAR(4000)
    DECLARE	@Where	NVARCHAR(1000)
    IF  ((@StartDate IS NOT NULL) AND (@EndDate IS NOT NULL) AND (@StartDate <= @EndDate))
	BEGIN
		SET @Where	=			 ' AND StartDate >= '''	+ CONVERT(VARCHAR, @StartDate, 121) + ''''
		SET @Where	=	@Where + ' AND EndDate <= '''	+ CONVERT(VARCHAR, @EndDate, 121) + ''''
	END
		
    
	IF	(@Name IS NOT NULL AND @Name != '')
		SET	@Where	=	@Where + ' AND Name LIKE ''%' + @Name + '%'''
	
	SET		@Sql	= 'SELECT [Id]
							  ,[Name]
							  ,[Description]
							  ,[StartDate]
							  ,[EndDate]
							  ,[CreateDate]
							  ,[ModifyDate]
							  ,[CreateBy]
							  ,[ModifyBy]
						  FROM [dbo].[Projects]
						WHERE		0	=	0
						' +	@Where
	EXECUTE	(@Sql)
END
Go

CREATE PROC Projects_GetById
	@Id			int
	AS
	BEGIN
		SELECT [Id]
			  ,[Name]
			  ,[Description]
			  ,[StartDate]
			  ,[EndDate]
			  ,[CreateDate]
			  ,[ModifyDate]
			  ,[CreateBy]
			  ,[ModifyBy]
		FROM [dbo].[Projects]
		WHERE Id=@Id
	END
GO

create proc AppointmentComments_GetAppId
@Id int
as 
begin
	select * from AppointmentComments where AppointmentId = @Id
end
go

create proc AppointmentComment_CheckExisted
@AppId int,
@UserId int
as 
begin
	select * from AppointmentComments where AppointmentId = @AppId and CreateBy = @UserId
end
go

create proc AppointmentComment_CountAccepted
@AppId int
as
begin 
	select COUNT(*) as total from AppointmentComments where AppointmentId = @AppId and CommentType = 0
end
GO

CREATE PROC [dbo].[AppointmentComments_Insert]
		 ( @CommentType		 int
		 ,@AppointmentId	int				
           ,@Comments		 nvarchar(500)
           ,@CreateDate		 datetime
           ,@ModifyDate		 datetime
           ,@CreateBy		 int
           ,@ModifyBy		 int
           ,@Id bigint OUTPUT)
AS
BEGIN
	INSERT INTO [dbo].[AppointmentComments]
	   (CommentType
	   ,AppointmentId
	   ,Comments
	   ,[CreateDate]
	   ,[ModifyDate]
	   ,[CreateBy]
	   ,[ModifyBy])
 VALUES
	   (@CommentType			
	   ,@AppointmentId	
	   ,@Comments		
	   ,@CreateDate		
	   ,@ModifyDate		
	   ,@CreateBy		
	   ,@ModifyBy	)	
	   
	   SELECT @Id = @@IDENTITY
	   
END
Go

create proc Documents_GetById
@Id int
as
begin 
	select * from Documents where Id = @Id
end
go

create proc TaskDocs_DeleteByTaskId
@Id int
as
begin
	delete from TaskDocs where TaskId = @Id
end
go

create proc TaskDocs_GetByTaskId
@Id int
as
begin
	select DocumentId from TaskDocs where TaskId = @Id
end
go

create proc ReportDocs_GetByTaskId
@Id int
as
begin
	select DocumentId from ReportDocs where ReportId = @Id
end
go
